package org.itworks.biodev.model;

import java.sql.*;
import java.text.*;
import java.util.*;
import java.util.Date;

public class SalariesTable {
	private static SalariesTable salariesTable;
		
	public static SalariesTable getInstance() {
		if(salariesTable == null) {
			salariesTable = new SalariesTable();
		}
		
		return salariesTable;
	}
	
	public float findSalaryForPeriod(Date from, Date to) throws Exception {
		
		Timestamp timestampFrom = new Timestamp(from.getTime());
		Timestamp timestampTo = new Timestamp(to.getTime());
		String subException = "ACCESSOIRE";
		float resAccesoire=0;
		float resAutre=0; 
		float resTotal=0;
		
		String sqlQuery = "SELECT SUM(SP.Quantity*SP.Price * P.VAT_RATE/100*0.1) AS SAL_ACC FROM PRODUCT_SUB_CATEGORY SC, PRODUCT P, SALE_PRODUCT SP, SALE S WHERE SP.PRODUCT_ID=P.ID AND P.SUB_CATEGORY_ID = SC.ID AND S.ID = SP.SALE_ID AND SC.UPPER(LABEL) LIKE '%"+subException+"%' AND S.DATETIME_SALE BETWEEN ? AND ?";
		PreparedStatement preparedStatementAccesoire = BiodevDatabase.getConnection().prepareStatement(sqlQuery);
		preparedStatementAccesoire.setString(1, timestampFrom.toString());
		preparedStatementAccesoire.setString(2, timestampTo.toString());
		ResultSet resultSet = preparedStatementAccesoire.executeQuery();
		
		if(resultSet.next() && resultSet.getObject("SAL_ACC") !=null) {

			resAccesoire=Float.parseFloat(resultSet.getObject("SAL_ACC").toString());
		}
		sqlQuery = "SELECT SUM(SP.Quantity*SP.Price *0.2) AS SAL_AUT FROM PRODUCT_SUB_CATEGORY SC, PRODUCT P, SALE_PRODUCT SP, SALE S WHERE SP.PRODUCT_ID=P.ID AND P.SUB_CATEGORY_ID = SC.ID AND S.ID= SP.SALE_ID AND SC.UPPER(LABEL) NOT LIKE '%"+subException+"%' AND S.DATETIME_SALE BETWEEN ? AND ?";
		PreparedStatement preparedStatementAutre = BiodevDatabase.getConnection().prepareStatement(sqlQuery);
		preparedStatementAutre.setString(1, timestampFrom.toString());
		preparedStatementAutre.setString(2, timestampTo.toString());
		ResultSet resultSet2 = preparedStatementAutre.executeQuery();
		
		if(resultSet2.next() && resultSet2.getObject("SAL_AUT") !=null) {
			resAutre=Float.parseFloat(resultSet2.getObject("SAL_AUT").toString());
		}
		
		resTotal=resAccesoire+resAutre;;


		return resTotal;
	}
}